package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IMarketReportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class MarketReportServiceImpl implements IMarketReportService {
    @Autowired
    private SqlMapper sqlMapper;

    //市场全院
    @Override
    public List<MarketReportInfo> getChannelList(SearchMarketReportInfo searchInfo) {
        String sqldept = "select id,abbreviation,simplename from dept ";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            sqldept += " where simplename='" + searchInfo.getArrivalStore() + "' ";
        } else {
            sqldept += " where simplename='" + ShiroKit.getUser().getOutpatientName() + "' ";
        }
        Dept dept = sqlMapper.selectOne(sqldept, Dept.class);
        String sql1 = " select t.id,t.one_channel as oneChannel,t.two_channel as twoChannel,t.thr_channel as thrChannel from reservations_infosc t where 1=1";
        String conditions = "";
        int a = 0;
        int b = 0;
        int c = 0;
        if (!"请选择".equals(searchInfo.getOneChannel()) && StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            a++;
            sql1 += " and t.one_channel='" + searchInfo.getOneChannel() + "'";
        }
        if (!"请选择".equals(searchInfo.getTwoChannel()) && StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            b++;
            sql1 += " and t.two_channel='" + searchInfo.getTwoChannel() + "'";
        }
        if (!"请选择".equals(searchInfo.getThrChannel()) && StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            c++;
            sql1 += " and t.thr_channel='" + searchInfo.getThrChannel() + "'";
        }
        if (a == 0) {
            sql1 += " group by t.one_channel order by t.one_channel ";
            conditions += "";
        } else {
            if (b == 0) {
                sql1 += " group by t.two_channel order by t.two_channel ";
                conditions += " and t.one_channel='" + searchInfo.getOneChannel() + "' ";
            } else {
                if (c == 0) {
                    sql1 += " group by t.thr_channel order by t.one_channel ,t.two_channel ";
                    conditions += " and t.one_channel='" + searchInfo.getOneChannel() + "' and t.two_channel='" + searchInfo.getTwoChannel() + "' ";
                } else {
                    sql1 += " group by t.thr_channel order by t.one_channel ,t.two_channel,t.thr_channel ";
                    conditions += " and t.one_channel='" + searchInfo.getOneChannel() + "' and t.two_channel='" + searchInfo.getTwoChannel() + "' " +
                            " and t.thr_channel='" + searchInfo.getThrChannel() + "' ";
                }
            }
        }
        List<SearchMarketReportInfo> list1 = sqlMapper.selectList(sql1, SearchMarketReportInfo.class);
        String sql = " ";
        for (int i = 0; i < list1.size(); i++) {
            String channel = "'" + list1.get(i).getOneChannel() + "' as oneChannel,'" + list1.get(i).getTwoChannel() + "' as twoChannel,'"
                    + list1.get(i).getThrChannel() + "' as thrChannel";
            String condition = "";
            if (a == 0) {
                condition += " and t.one_channel='" + list1.get(i).getOneChannel() + "' ";
            } else {
                if (b == 0) {
                    condition += " and t.one_channel='" + list1.get(i).getOneChannel() + "' and t.two_channel='" + list1.get(i).getTwoChannel() + "' ";
                } else {
                    if (c == 0) {
                        condition += " and t.one_channel='" + list1.get(i).getOneChannel() + "' and t.two_channel='" + list1.get(i).getTwoChannel() + "' " +
                                " and t.thr_channel='" + list1.get(i).getThrChannel() + "'";
                    } else {
                        condition += " and t.one_channel='" + list1.get(i).getOneChannel() + "' and t.two_channel='" + list1.get(i).getTwoChannel() + "' " +
                                " and t.thr_channel='" + list1.get(i).getThrChannel() + "'";
                    }
                }
            }
            sql += this.getsqlDepartment(searchInfo, channel, condition, i, list1, dept.getAbbreviation(), dept.getSimplename());
        }
        if(list1.size()>0){
            sql += " union all ";
        }
        String channels = "'合计' as oneChannel,'' as twoChannel,'' as thrChannel";
        sql += this.getTotleDepartmentSql(searchInfo, channels, conditions, dept.getAbbreviation());
        List<MarketReportInfo> list = sqlMapper.selectList(sql, MarketReportInfo.class);
        return list;
    }

    public String getsqlDepartment(SearchMarketReportInfo searchInfo, String channel, String condition,
                                   int i, List<SearchMarketReportInfo> list1, String abbreviation, String simplename) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "' limit 1";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = "   select " + channel + " , " +
                " (select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and filing_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "' " + condition + " group by t.id)t) AS filingNumber," +
                " (select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and appointment_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "' " + condition + " group by t.id)t) AS appointmentNumber," +
                " (select count(1) from (select t.id from reservations_infosc t where t.first_visit_date>='" + searchInfo.getStartTime()
                + " ' and first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + simplename + "' " + condition + " group by t.id)t) as customerxkNumber," +
                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null " + condition + " group by t.reservations_id) b) as customerNumber, " +
                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null " + condition + " group by t.reservations_id) b) as volume," +
                " (select SUM(t.sectionerformance) from (select t1.hospital_performance as sectionerformance from reservations_infosc t " +
                " left join " + tableName3 + " t1 on t.id=t1.reservations_id where " +
                " t1.recharge_time <= '" + searchInfo.getEndTime() + "' and t1.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " " + condition + " group by t1.id) t) as channelPerformance  " +
                " from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getTotleDepartmentSql(SearchMarketReportInfo searchInfo, String channel, String condition, String abbreviation) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "' limit 1";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select " + channel + " , " +
                " (select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and filing_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + dept.getSimplename() + "' " + condition + " group by t.id)t) AS filingNumber," +
                " (select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and appointment_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + dept.getSimplename() + "' " + condition + " group by t.id)t) AS appointmentNumber," +
                " (select count(1) from (select t.id from reservations_infosc t where t.first_visit_date>='" + searchInfo.getStartTime()
                + " ' and first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + dept.getSimplename() + "' " + condition + " group by t.id)t) as customerxkNumber," +
                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null " + condition + " group by t.reservations_id) b) as customerNumber, " +
                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.scarchiving is not null and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                "  " + condition + " group by t.reservations_id) b) as volume," +
                " (select SUM(t.sectionerformance) from (select t1.hospital_performance as sectionerformance from reservations_infosc t " +
                " left join " + tableName3 + " t1 on t.id=t1.reservations_id where t1.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " and t1.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " " + condition + " group by t1.id) t) as channelPerformance  " +
                " from dual ";
        return sql;
    }

    //市场人员
    @Override
    public List<MarketReportInfo> getMarketPersonnel(SearchMarketReportInfo searchInfo) {
        String sqldept = "select id,simplename,abbreviation from dept ";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            sqldept += " where simplename='" + searchInfo.getArrivalStore() + "'";
        } else {
            sqldept += " where simplename='" + ShiroKit.getUser().getOutpatientName() + "'";
        }
        Dept dept = sqlMapper.selectOne(sqldept, Dept.class);
        String sql1 = " select t.id,t.archiving from reservations_infosc t left join "+dept.getAbbreviation()+"customer_info t1 on t.id=t1.reservations_id where " +
                " ((t1.arrival_time>='" + searchInfo.getStartTime() + "' and t1.arrival_time<='" + searchInfo.getEndTime() + "') or " +
                " (t.filing_time>='" + searchInfo.getStartTime() + "' and t.filing_time<='" + searchInfo.getEndTime() + "') or " +
                " (t.appointment_time>='" + searchInfo.getStartTime() + "' and t.appointment_time<='" + searchInfo.getEndTime() + "'))  " +
                " and (t.arrival_store ='"+dept.getSimplename()+"' or t.outpatient_name='"+dept.getSimplename()+"') group by t.archiving";
        List<SearchMarketReportInfo> list1 = sqlMapper.selectList(sql1, SearchMarketReportInfo.class);
        String sql = "";
        for (int i = 0; i < list1.size(); i++) {
            String classification = list1.get(i).getArchiving();
            String channel = "'" + list1.get(i).getArchiving() + "' as archiving";
            sql += this.getsqlMarketPersonnel(searchInfo, channel, classification, i, list1, dept.getSimplename());
        }
        if(list1.size()>0){
            sql += " union all ";
        }
        String channel = "'合计' as archiving ";
        sql += this.getsqlHospitalWide(searchInfo, channel, dept.getSimplename());
        List<MarketReportInfo> list = sqlMapper.selectList(sql, MarketReportInfo.class);
        return list;
    }

    public String getsqlMarketPersonnel(SearchMarketReportInfo searchInfo, String channel, String classification,
                                        int i, List<SearchMarketReportInfo> list1, String simplename) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "' limit 1";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select " + channel + " , " +
                " (select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and filing_time <='" + searchInfo.getEndTime() + "' and archiving='" + classification + "' and t.outpatient_name='" + simplename + "'" +
                " group by t.id)t) AS filingNumber," +

                " (select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and appointment_time <='" + searchInfo.getEndTime() + "' and archiving='" + classification + "' and t.outpatient_name='" + simplename + "' " +
                " group by t.id)t) AS appointmentNumber," +

                " (select count(1) from (select t.id from reservations_infosc t left join " + tableName2 + " t1 on t.id=t1.reservations_id where t.first_visit_date>='" + searchInfo.getStartTime()
                + "' and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.archiving='" + classification + "' and t.arrival_store='" + simplename + "' " +
                " group by t.id) t) as customerxkNumber," +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving='" + classification + "' group by t.reservations_id) b) as customerNumber, " +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving='" + classification + "' group by t.reservations_id) b) as volume," +

                " (select SUM(t.sectionerformance) from (select t.hospital_performance as sectionerformance from reservations_infosc t1 " +
                " left join " + tableName3 + " t on t1.id=t.reservations_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " and t.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " and t1.archiving='" + classification + "' group by t.id) t) as channelPerformance  " +

                " from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getsqlHospitalWide(SearchMarketReportInfo searchInfo, String channel, String simplename) {
        String getabbreviationsql = "select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "' limit 1";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select " + channel + " , " +
                " (select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and t.filing_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "'" +
                " group by t.id) t) AS filingNumber," +

                " (select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and t.appointment_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "' " +
                " group by t.id)t) AS appointmentNumber," +

                " (select count(1) from (select t.id from reservations_infosc t left join " + tableName2 + " t1 on t.id=t1.reservations_id " +
                " where t.first_visit_date>='" + searchInfo.getStartTime() + "' " +
                " and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + simplename + "' " +
                " group by t.id)t) as customerxkNumber," +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null group by t.reservations_id) b) as customerNumber, " +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null group by t.reservations_id) b) as volume," +

                " (select SUM(t.sectionerformance) from (select t.hospital_performance as sectionerformance from reservations_infosc t1 " +
                " left join " + tableName3 + " t on t1.id=t.reservations_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " and t.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " group by t.id) t) as channelPerformance  " +

                " from dual ";
        return sql;
    }
}